<?php
class model_transportprice extends framework_base_model {
	/**
	 * @desc 添加地区
	 * @param array 
	 * @return number
	 */
	public static function transportprice_add($data) {
    	if(!is_array($data)) {
    		return 0;
    	}
    	try{
    		$sql = "insert into `weixin_transport_price` set ";
    		$binds = array();
    		foreach ($data as $k => $v) {
    			$sql .= '`' . $k . '`=:' . $k . ',';
    			$binds[':' . $k] = $v;
    		}
    		$sql .= 'raw_add_time=NOW()';
    		$arr=self::DB() -> dbh -> prepare($sql);
    		$rel = $arr->execute($binds);
    		if($rel == TRUE) {
    			return self::DB()->dbh->lastInsertId();
    		} else {
    			return 0;
    		}
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return 0;
    	}
    }
	/*
     * 判断该运输模板是否还存在
     */
    public static function transportprice_exists($tmp_id, $area_id, $min_weight,$id = 0) {
    	$info = array();

    	$sql = "select `transport_price_id` from `weixin_transport_price` where `transport_tmp_id` = :transport_tmp_id and `area_id` = :area_id and `min_weight`=:min_weight and `delete_flag` = 0 ";
		$binds[":transport_tmp_id"] = $tmp_id;
		$binds[":area_id"] = $area_id;
		$binds[":min_weight"] = $min_weight;
		if(!empty($id)){
			$sql .= " and `transport_price_id` != :transport_price_id";
			$binds[":transport_price_id"] = $id;
		}
    	try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute($binds);
    		$info = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $info;
    }
	/*
     * 根据id是否存在该分类名称
     */
    public static function get_transportprice_by_id($id) {
    	$info = array();
		$id = intval($id);
    	if(empty($id)) {
    		return $info;
    	}
    	$sql = "select `transport_price_id`,`transport_tmp_id`,`area_id`,`min_weight`,`max_weight`,`transport_price`,`author`,`editer`,`raw_add_time`,`raw_update_time` from `weixin_transport_price` where `transport_price_id`=:transport_price_id and delete_flag = 0";
    	try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute(array(":transport_price_id" => $id));
    		$info = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $info;
    }
	public static function get_min_weight($area_id, $tmp_id){
		$info = array();
		$sql = "select `max_weight` from `weixin_transport_price` where `area_id`=:area_id and `transport_tmp_id` = :transport_tmp_id and delete_flag = 0 order by `min_weight` desc limit 1";
    	try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute(array(":area_id"=>$area_id, ":transport_tmp_id" => $tmp_id));
    		$info = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $info;
	}
	//根据模板ID获取运费模板
	public static function get_transportprice_by_tmp_id($id) {
    	$info = array();
		$id = intval($id);
    	if(empty($id)) {
    		return $info;
    	}
    	$sql = "select `transport_price_id`,`transport_tmp_id`,`area_id`,`min_weight`,`max_weight`,`transport_price`,`author`,`editer`,`raw_add_time`,`raw_update_time` from `weixin_transport_price` where `transport_tmp_id`=:transport_tmp_id and delete_flag = 0";
    	try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute(array(":transport_tmp_id" => $id));
    		$info = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $info;
    }
    /**
     * @desc 更新分类
     * @param unknown $id
     * @param unknown $data
     * @return unknown|number
     */
    public static function update_transportprice_info($id,$data) {
    	try{
    		$sql = 'UPDATE `weixin_transport_price` SET ';
    		$binds = array();
    		foreach ($data as $k => $v) {
    			$sql .= '`' . $k . '`=:' . $k . ',';
    			$binds[':' . $k] = $v;
    		}
    		$ids = implode(',',$id);
    		$sql = trim($sql,',');
    		$sql .= ' WHERE `transport_price_id` in ('.$ids.')';
    		$stm=self::DB() -> dbh -> prepare($sql);
    		$rel = $stm->execute($binds);
    		$rowCount = $stm -> rowCount();
    		return $rowCount;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return -1;
    	}
    }
    //汽运模板数据
    public static function get_transportprice_motor($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$sql = "select c.`transport_price_id`,a.`province`,a.`city`,b.`area`,b.`street`,c.`min_weight`,c.`max_weight`,c.`transport_price`,c.`author`,c.`raw_add_time` from `weixin_area` as a right join `weixin_area_motor` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_motor_id` = c.`area_id` where c.`delete_flag` = 0 ";
		$where = "";
    	if(is_array($condition)&& count($condition)>0){
			if(isset($condition['tmp_id'])) {
    			$where .= " and c.`transport_tmp_id` = {$condition['tmp_id']}";
    		}
    	}
    	$where .= " order by c.`area_id` ,c.`min_weight` asc";
    	$limit = "";
        if($pagesize != 0 ){
    		$limit = " limit {$nextNum},{$pagesize}";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql.$where.$limit);
    		$st->execute();
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result = array();
    	}
		
		$sql = "select count(c.`transport_price_id`) from `weixin_area` as a right join `weixin_area_motor` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_motor_id` = c.`area_id` where c.`delete_flag` = 0 {$where}";
		$binds2 = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds2);
    		$count = $st->fetchColumn();
    		$result['count'] = $count;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result['count'] = 0;
    	}		
		return $result;
    }
	//水运模板数据
	public static function get_transportprice_ship($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$sql = "select c.`transport_price_id`,a.`province`,a.`city`,b.`port`,b.`port_dest`,c.`min_weight`,c.`max_weight`,c.`transport_price`,c.`author`,c.`raw_add_time` from `weixin_area` as a right join `weixin_area_ship` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_ship_id` = c.`area_id` where c.`delete_flag` = 0 ";
		$where = "";
    	if(is_array($condition)&& count($condition)>0){
			if(isset($condition['tmp_id'])) {
    			$where .= " and c.`transport_tmp_id` = {$condition['tmp_id']}";
    		}
    	}
    	$where .= " order by c.`area_id` ,c.`min_weight` asc";
    	$limit = "";
        if($pagesize != 0 ){
    		$limit = " limit {$nextNum},{$pagesize}";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql.$where.$limit);
    		$st->execute();
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result = array();
    	}
		
		$sql = "select count(c.`transport_price_id`) from `weixin_area` as a right join `weixin_area_ship` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_ship_id` = c.`area_id` where c.`delete_flag` = 0 {$where}";
		$binds2 = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds2);
    		$count = $st->fetchColumn();
    		$result['count'] = $count;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result['count'] = 0;
    	}		
		return $result;
    }
	//火运模板数据
	public static function get_transportprice_train($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$sql = "select c.`transport_price_id`,a.`province`,a.`city`,b.`area_station`,c.`min_weight`,c.`max_weight`,c.`transport_price`,c.`author`,c.`raw_add_time` from `weixin_area` as a right join `weixin_area_train` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_train_id` = c.`area_id` where c.`delete_flag` = 0 ";
		$where = "";
    	if(is_array($condition)&& count($condition)>0){
			if(isset($condition['tmp_id'])) {
    			$where .= " and c.`transport_tmp_id` = {$condition['tmp_id']}";
    		}
    	}
    	$where .= " order by c.`area_id` ,c.`min_weight` asc";
    	$limit = "";
        if($pagesize != 0 ){
    		$limit = " limit {$nextNum},{$pagesize}";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql.$where.$limit);
    		$st->execute();
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result = array();
    	}
		
		$sql = "select count(c.`transport_price_id`) from `weixin_area` as a right join `weixin_area_train` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_train_id` = c.`area_id` where c.`delete_flag` = 0 {$where}";
		$binds2 = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds2);
    		$count = $st->fetchColumn();
    		$result['count'] = $count;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result['count'] = 0;
    	}		
		return $result;
    }
	//水汽联运运模板数据
	public static function get_transportprice_smotor($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$sql = "select c.`transport_price_id`,a.`province`,a.`city`,b.`area_wharf`,c.`min_weight`,c.`max_weight`,c.`transport_price`,c.`author`,c.`raw_add_time` from `weixin_area` as a right join `weixin_area_smotor` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_smotor_id` = c.`area_id` where c.`delete_flag` = 0 ";
		$where = "";
    	if(is_array($condition)&& count($condition)>0){
			if(isset($condition['tmp_id'])) {
    			$where .= " and c.`transport_tmp_id` = {$condition['tmp_id']}";
    		}
    	}
    	$where .= " order by c.`area_id` ,c.`min_weight` asc";
    	$limit = "";
        if($pagesize != 0 ){
    		$limit = " limit {$nextNum},{$pagesize}";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql.$where.$limit);
    		$st->execute();
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result = array();
    	}
		
		$sql = "select count(c.`transport_price_id`) from `weixin_area` as a right join `weixin_area_smotor` as b on a.`area_id` = b.`area_id` right join `weixin_transport_price` as c on b.`area_smotor_id` = c.`area_id` where c.`delete_flag` = 0 {$where}";
		$binds2 = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds2);
    		$count = $st->fetchColumn();
    		$result['count'] = $count;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result['count'] = 0;
    	}		
		return $result;
    }
	//获取选择的地区ID列表
	public static function get_select_list($tmp_id){
		$sql = "select `area_id` from `weixin_transport_price` where `transport_tmp_id`=:transport_tmp_id and `delete_flag`=0";
		$return = array();
		try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute(array(':transport_tmp_id'=>$tmp_id));
    		$result = $st -> fetchAll();
			if(!empty($result)){
				foreach($result as $value){
					$return[] = $value['area_id'];
				}
			}
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $return;
	}
	/*
	* 更新模板地址
	* @tmp_id 模板ID
	* @areas 地区ID集合
	* @section 吨位区间集合
	**/
	public static function modify_tmp_addr($tmp_id, array $areas, array $section){
		if(empty($tmp_id) || empty($areas) || empty($section)){
			return false;
		}
		//删除全部模板地址
		$sql1 = "update `weixin_transport_price` set `delete_flag`=1 where `transport_tmp_id`=:transport_tmp_id and `delete_flag`=0";
		//恢复某个地区模板
		$sql2 = "select `transport_price_id` from `weixin_transport_price` where `transport_tmp_id`=:transport_tmp_id and `area_id`=:area_id and `delete_flag`=1";
		//修改地区模板吨位区间
		$sql3 = "update `weixin_transport_price` set `min_weight`=:min_weight,`max_weight`=:max_weight,`delete_flag`=0 where `transport_price_id`=:transport_price_id and `delete_flag`=1";
		//写入不存在的模板地址
		$sql4 = "insert into `weixin_transport_price` set `transport_tmp_id`=:transport_tmp_id,`area_id`=:area_id,`min_weight`=:min_weight,`max_weight`=:max_weight,`author`=:author,`editer`=:editer,`raw_add_time`=NOW()";
		//查询主键ID
		$sql5 = "select `transport_price_id` from `weixin_transport_price` where `transport_tmp_id`=:transport_tmp_id and `area_id`=:area_id and `delete_flag`=1 order by `transport_price_id` asc limit 1";
		try {
			//开启事务
			$pdo = self::DB()->dbh;
			$pdo->beginTransaction();
    		$st1 = $pdo->prepare($sql1);
			$st2 = $pdo->prepare($sql2);
    		$st1->execute(array(':transport_tmp_id'=>$tmp_id));
			foreach($areas as $value){
				$st2->execute(array(':transport_tmp_id'=>$tmp_id, ':area_id'=>$value));
				$count = $st2->fetchAll();
				//不存在的模板地址插入数据
				if(empty($count)){
					$st4 = $pdo->prepare($sql4);
					foreach($section as $v){
						$arr = array();
						$arr[':transport_tmp_id'] = $tmp_id;
						$arr[':area_id'] = $value;
						$arr[':min_weight'] = $v['min'];
						$arr[':max_weight'] = $v['max'];
						$arr[':author'] = $_SESSION['admin']['username'];
						$arr[':editer'] = $_SESSION['admin']['username'];
						$st4->execute($arr);
					}
				//存在的模板地址修改数据
				}else{
					$st3 = $pdo->prepare($sql3);
					$st5 = $pdo->prepare($sql5);
					foreach($section as $k=>$v){
						//查询最小模板ID
						$st5->execute(array(':transport_tmp_id'=>$tmp_id, ':area_id'=>$value));
						$result = $st5 -> fetch();
						//不存在写入数据
						if(empty($result)){
							$st4 = $pdo->prepare($sql4);
							$arr = array();
							$arr[':transport_tmp_id'] = $tmp_id;
							$arr[':area_id'] = $value;
							$arr[':min_weight'] = $v['min'];
							$arr[':max_weight'] = $v['max'];
							$arr[':author'] = $_SESSION['admin']['username'];
							$arr[':editer'] = $_SESSION['admin']['username'];
							$st4->execute($arr);			
						}else{
							$arr = array();
							$arr[':transport_price_id'] = $result['transport_price_id'];
							$arr[':min_weight'] = $v['min'];
							$arr[':max_weight'] = $v['max'];
							$st3->execute($arr);
						}
					}
				}
			}
    		$pdo->commit();
			return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
			return false;
    	}
	}
	//保存模板数据
	public static function save_table_data($tmp_id, $data){
		if(empty($tmp_id) || empty($data)){
			return false;
		}
		$sql = "update `weixin_transport_price` set `transport_price`=:transport_price where `transport_price_id`=:transport_price_id and `transport_tmp_id`=:transport_tmp_id and `delete_flag`=0";
		try {
			//开启事务
			$pdo = self::DB()->dbh;
			$pdo->beginTransaction();
			$st = $pdo->prepare($sql);
			$arr = array();
			foreach($data as $key=>$value){
				$arr[':transport_price_id'] = $key;
				$arr[':transport_tmp_id'] = $tmp_id;
				$arr[':transport_price'] = $value;
				$st->execute($arr);
			}
			$pdo->commit();
			return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
			return false;
    	}
	}
}
